package cn.strategy.fund;

import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;

public class FundZhang {

    @Test
    public void updateAll_ex() throws SQLException, ClassNotFoundException {

        Class.forName("org.h2.Driver");
        Connection conn = DriverManager.getConnection("jdbc:h2:~/data;AUTO_SERVER=TRUE", "root", "root");

        String sql = "SELECT symbol FROM type_stock GROUP BY symbol";
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        Hashtable<String, String> ht = new Hashtable<>();
        while (rs.next()) {
            String key = find_zhangting(conn, rs.getString(1));
            if (key != null && !ht.containsKey(key)) {
                ht.put(key, key);
            }
        }

        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
        if (conn != null) conn.close();

        System.out.println("********************");
        for (String key: ht.keySet()) {
            System.out.println(key);
        }
    }

    public String find_zhangting(Connection conn, String symbol) {
        String sql = "select symbol, name, zhangdie, huanshoulv, stock_date from df_stock_date where symbol = '"+ symbol +"' order by stock_date desc limit 0, 4";
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sname = null;

        try {
            stmt = conn.prepareStatement(sql);

            // 5. 执行查询并获取结果集
            rs = stmt.executeQuery();

            //List<String> lst = new ArrayList<>();
            // 6. 遍历结果集并处理数据
            while (rs.next()) {
                if (rs.getDouble(3) > 9.9D && rs.getDouble(4) < 25D && rs.getDouble(4) > 10D) {
                    System.out.println(rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(5));
                    //lst.add(rs.getString(2));
                    sname = rs.getString(2);
                } else {
                    break;
                }
            }

//            System.out.println("****************");
//            if (lst.size() > 1) {
//                System.out.println(lst.get(0));
//            }

        } catch (SQLException e) {
            System.err.println("数据库操作失败！错误信息：" + e.getMessage());
            e.printStackTrace();
        } finally {
            // 7. 关闭资源（反向顺序）
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return sname;
    }
}
